package com.yunquan.ohana.db

import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import com.yunquan.ohana.utils.Utils

class DBManager : SQLiteOpenHelper(Utils.instance.context, DB_NAME, null, DB_VERSION) {

    companion object {
        private const val DB_NAME = "ohana.db"
        private const val DB_VERSION = 2

        const val TABLE_PHOTO = "photo"
        const val TABLE_FRAMES = "frames"
        const val TABLE_PHOTO_FRAMES = "photo_frames"
        const val TABLE_WEATHER_LOCATION = "weather_location"

        val instance by lazy(LazyThreadSafetyMode.SYNCHRONIZED) { DBManager() }

    }

    override fun onCreate(db: SQLiteDatabase?) {
        // 设备表
        createFrames(db)
        // 设备与文件关联表
        createPhotoFrames(db)
        // 文件表
        createPhotos(db)
        // 天气位置表
        createWeatherLocation(db)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        if (newVersion == 2) {
            createWeatherLocation(db)
        }
    }

    /**
     * 设备表
     */
    private fun createFrames(db: SQLiteDatabase?) {
        db?.execSQL(
            "CREATE TABLE IF NOT EXISTS $TABLE_FRAMES (" +
                    "device_id TEXT PRIMARY KEY," + // 设备ID
                    "name TEXT," + // 设备名称
                    "location TEXT," + // 设备位置
                    "online INTEGER" + // 在线状态
                    ");"
        )
    }

    /**
     * 设备与文件关联表
     */
    private fun createPhotoFrames(db: SQLiteDatabase?) {
        db?.execSQL(
            "CREATE TABLE IF NOT EXISTS $TABLE_PHOTO_FRAMES (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," + // 本地存储ID
                    "group_id TEXT," + // 分组ID
                    "frames_id TEXT," + // 设备ID
                    "status INTEGER" + // 接收状态
                    ");"
        )
    }

    /**
     * 文件表
     */
    private fun createPhotos(db: SQLiteDatabase?) {
        db?.execSQL(
            "CREATE TABLE IF NOT EXISTS $TABLE_PHOTO (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," + // 本地存储ID
                    "service_id TEXT," + // 服务器ID
                    "group_id TEXT," + // 本地分组ID
                    "service_group_id TEXT," + // 服务器组ID
                    "content TEXT," + // 祝福语
                    "file_name TEXT," + // 文件名称
                    "file_url TEXT," + // 文件URL
                    "local_file_id TEXT," + // 文件本地ID
                    "local_file_path TEXT," + // 文件本地路径
                    "local_crop_id TEXT," + // 文件裁剪后本地ID
                    "local_crop_path TEXT," + // 文件裁剪后本地路径
                    "video_img_url TEXT," + // 视频封面URL
                    "thumbnail TEXT," + // 视频封面本地路径
                    "duration INTEGER," + // 视频时长
                    "height INTEGER," + // 文件高
                    "width INTEGER," + // 文件宽
                    "status INTEGER," + // 发送状态 0-未发送 1-已发送 2-发送失败
                    "type INTEGER," + // 文件类型 1-图片 2-视频
                    "create_time TEXT" + // 创建时间
                    ");"
        )
    }

    /**
     * 天气位置表
     */
    private fun createWeatherLocation(db: SQLiteDatabase?) {
        // 天气位置表
        db?.execSQL(
            "CREATE TABLE IF NOT EXISTS $TABLE_WEATHER_LOCATION (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," + // 本地存储ID
                    "user_id TEXT," + // 用户ID
                    "location_id TEXT," + // 定位 id
                    "name TEXT," + // 城市名称
                    "longitude TEXT," + // 经度
                    "latitude TEXT," + // 纬度
                    "time_zone TEXT," + // 时区
                    "utc_offset TEXT" + // 地区/城市目前与UTC时间偏移的小时数，参考详细说明
                    ");"
        )
    }
}